use sqlite_wasm_rs::{sqlite3, sqlite3_bind_int, sqlite3_close, sqlite3_column_count, sqlite3_column_type, sqlite3_exec, sqlite3_finalize, sqlite3_prepare_v3, sqlite3_step, sqlite3_stmt, SQLITE_FLOAT, SQLITE_INTEGER, SQLITE_OK, SQLITE_ROW, SQLITE_TEXT};
use crate::db::db::{print_error, Db};
use crate::db::row::row_wrap;
use crate::db::util::{db_get_double, db_get_int, db_get_int64, db_get_string};

pub fn create_db() {
    log::info!("create db");
    let db = Db::open();
    let sql = c"
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            salary REAL NOT NULL
        );

        INSERT INTO employees (name, salary) VALUES ('Alice', 50000);
        INSERT INTO employees (name, salary) VALUES ('Bob', 60000);
        UPDATE employees SET salary = 55000 WHERE id = 1;
        ";

    let ret = unsafe {
        sqlite3_exec(
            db.db(),
            sql.as_ptr().cast(),
            None,
            std::ptr::null_mut(),
            std::ptr::null_mut(),
        )
    };
    assert_eq!(SQLITE_OK, ret);

    unsafe {
        sqlite3_close(db.db());
    }
}

#[derive(Debug)]
pub struct Employee {
    pub id: u64,
    pub name: String,
    pub salary: f64,
}

pub fn query()->Vec<Employee> {
    log::info!("query db");
    let db = Db::open();

    // check_result(db);
    let result = query_data(db.db());

    unsafe {
        sqlite3_close(db.db());
    }

    result
}

fn query_data(db: *mut sqlite3) -> Vec<Employee> {
    let sql = c"SELECT * FROM employees where id = ?;";
    let mut stmt = std::ptr::null_mut();
    let ret = unsafe {
        let ret = sqlite3_prepare_v3(
            db,
            sql.as_ptr().cast(),
            -1,
            0,
            &mut stmt as *mut _,
            std::ptr::null_mut(),
        );
        print_error(db, ret, "example");
        ret
    };
    assert_eq!(ret, SQLITE_OK);

    let mut result = vec![];
    unsafe {
        log::info!("query db111");
        sqlite3_bind_int(stmt, 1, 2);
        let employees = row_wrap(db, "employee query", stmt, Box::new(employee_wrap));
        result.extend(employees)
    }
    result
}

fn employee_wrap(stmt: *mut sqlite3_stmt)-> Employee {
    unsafe  {
        let id = db_get_int64(stmt, 0);
        let name = db_get_string(stmt, 1).unwrap();
        let salary = db_get_double(stmt, 2);
        Employee{
            id,
            name,
            salary
        }
    }
}

pub fn check_result(db: *mut sqlite3) {
    let sql = c"SELECT * FROM employees;";
    let mut stmt = std::ptr::null_mut();
    let ret = unsafe {
        sqlite3_prepare_v3(
            db,
            sql.as_ptr().cast(),
            -1,
            0,
            &mut stmt as *mut _,
            std::ptr::null_mut(),
        )
    };
    assert_eq!(ret, SQLITE_OK);

    let ret = [(1, "Alice", 55000.0), (2, "Bob", 60000.0)];
    let mut idx = 0;

    unsafe {
        log::info!("query db111");
        while sqlite3_step(stmt) == SQLITE_ROW {
            log::info!("query db2222");
            let count = sqlite3_column_count(stmt);
            for col in 0..count {
                // let name = sqlite3_column_name(stmt, col);
                // let sname = CStr::from_ptr(name).to_str().unwrap();
                // match sname {
                //     "name" => {
                //         let s = CStr::from_ptr(sqlite3_column_text(stmt, col).cast())
                //                     .to_str()
                //                     .unwrap();
                //         log::info!("query value: {}", s);
                //     },
                //     "id" => log::info!("query value: {}", sqlite3_column_int(stmt, col)),
                //     "salary" => log::info!("query value: {}", sqlite3_column_double(stmt, col)),
                //     &_ => todo!(),
                // }
                // log::info!("query db333, {}", CStr::from_ptr(name).to_str().unwrap());
                let ty: std::os::raw::c_int = sqlite3_column_type(stmt, col);
                let vty: i32 = ty;
                log::info!("{} query db333", ty);
                match vty {
                    SQLITE_INTEGER => assert_eq!(ret[idx].0, db_get_int(stmt, col)),
                    SQLITE_TEXT => {
                        let s = db_get_string(stmt, col).unwrap();
                        assert!(s == ret[idx].1);
                    }
                    SQLITE_FLOAT => assert_eq!(ret[idx].2, db_get_double(stmt, col)),
                    _ => (),
                }
            }
            idx += 1;
        }
        sqlite3_finalize(stmt);
    }
}